﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Icoders.MarketTracker.Core.MasterData;
using Icoders.MarketTracker.Data.EF;
using Icoders.MarketTracker.Data.Utils;
using Icoders.MarketTracker.Reports.Lib.Domain;
using Icoders.MarketTracker.Reports.Lib.Domain.Transactions;
using Icoders.MarketTracker.Reports.Lib.Domain.Users;

namespace Icoders.MarketTracker.Reports.Lib.Repository.Transactions.Impl
{
    public class ReportRepository : IReportRepository
    {
        private AppDataContext _ctx;

        public ReportRepository(AppDataContext ctx)
        {
            _ctx = ctx;
        }

        public List<PurchaseReport> GetReport(DateTime? startDate, DateTime? endDate)
        {
            return _ctx.TblPurchaseItem.OrderBy(o => o.TblPurchase.CreatedOn).Select(s => new PurchaseReport
            {
                Product = s.TblProduct.Name,
                Quantity = s.Quantity.Value*s.TblProduct.Milles.Value,
            }).ToList();
        }

        public List<SaleReport> GetSaleReport(DateTime? startDate, DateTime? endDate, int product, int country, int city, int area, int zone, int fsr, int pop)
        {
            //return _ctx.TblPurchaseItem.Select(s => new SaleReport
            //{
            //    Product = s.TblProduct.Name,
            //    Quantity = s.Quantity.Value,
            //    SaleRep = s.TblPurchase.TblUser.Username,
            //    Area = s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.Name,
            //    Pop = s.TblPurchase.TblPointOfPurchase.Name
            //}).ToList();

            IQueryable<TblPurchaseItem> query = _ctx.TblPurchaseItem.OrderBy(o => o.TblPurchase.CreatedOn);
            if (startDate != null && endDate != null)
            {
                endDate = endDate.Value.AddDays(1);
                query = query.Where(s => s.CreatedOn >= startDate.Value && s.CreatedOn <= endDate.Value);
            }
            if (product != 0)
            {
                query = query.Where(s => s.TblProduct.Id == product);
            }
            if (country != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.TblCountry.Id == country);
            }
            if (city != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.Id == city);
            }
            if (area != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.Id == area);
            }
            if (zone != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.Id == zone);
            }
            if (pop != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.Id == pop);
            }
            if (fsr != 0)
            {
                query = query.Where(s => s.TblPurchase.TblUser.Id == fsr);
            }

            return query.Select(s => new SaleReport
            {
                Product = s.TblProduct.Name,
                Quantity = s.Quantity.Value*s.TblProduct.Milles.Value,
                SaleRep = s.TblPurchase.TblUser.FirstName + " " + s.TblPurchase.TblUser.LastName,
                Area = s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.Name,
                Pop = s.TblPurchase.TblPointOfPurchase.Name
            }).ToList();
        }

        public List<PointOfPurchaseReport> GetPointOfPurchase(int areaId, int countryId, int cityId, int zoneId)
        {
            return _ctx.TblPointOfPurchase.Select(s => new PointOfPurchaseReport
            {
                Name = s.Name,
                City = s.TblZone.TblArea.TblCity.Name,
                Area = s.TblZone.TblArea.Name,
                Country = s.TblZone.TblArea.TblCity.TblCountry.Name,
                Zone = s.TblZone.Name
            }).ToList();
        }

        public List<SaleVolume> GetTotalSaleVoume(DateTime? startDate, DateTime? endDate, int product, int country, int city, int area, int zone, int fsr, int pop)
        {
            IQueryable<TblPurchaseItem> query = _ctx.TblPurchaseItem.OrderBy(o => o.TblPurchase.CreatedOn);
            if (startDate != null && endDate != null)
            {
                endDate = endDate.Value.AddDays(1);
                query = query.Where(s => s.CreatedOn >= startDate.Value && s.CreatedOn <= endDate.Value);
            }
            if (product != 0)
            {
                query = query.Where(s => s.TblProduct.Id == product);
            }
            if (country != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.TblCountry.Id == country);
            }
            if (city != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.Id == city);
            }
            if (area != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.Id == area);
            }
            if (zone != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.Id == zone);
            }
            if (pop != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.Id == pop);
            }
            if (fsr != 0)
            {
                query = query.Where(s => s.TblPurchase.TblUser.Id == fsr);
            }

            return query.GroupBy(p => new { p.CreatedOn.Year, p.CreatedOn.Month, p.CreatedOn.Day }).ToList().Select(s => new SaleVolume
            {
                Quantity = s.Sum(p => p.Quantity.Value*p.TblProduct.Milles.Value),
                Date = new DateTime(s.Key.Year, s.Key.Month, s.Key.Day)
            }).ToList();
        }

        public List<SalePerSku> GetSalePerSku(DateTime? startDate, DateTime? endDate, int product, int country, int city, int area, int zone, int fsr, int pop)
        {
            IQueryable<TblPurchaseItem> query = _ctx.TblPurchaseItem.OrderBy(o => o.TblPurchase.CreatedOn);
            if (startDate != null && endDate != null)
            {
                endDate = endDate.Value.AddDays(1);
                query = query.Where(s => s.CreatedOn >= startDate.Value && s.CreatedOn <= endDate.Value);
            }
            if (product != 0)
            {
                query = query.Where(s => s.TblProduct.Id == product);
            }
            if (country != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.TblCountry.Id == country);
            }
            if (city != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.Id == city);
            }
            if (area != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.Id == area);
            }
            if (zone != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.Id == zone);
            }
            if (pop != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.Id == pop);
            }
            if (fsr != 0)
            {
                query = query.Where(s => s.TblPurchase.TblUser.Id==fsr);
            }
            return query.GroupBy(g => new { g.ProductId, g.TblProduct.Name }).Select(s => new SalePerSku
                                                                  {
                                                                      Quantity = s.Sum(m => m.Quantity.Value*m.TblProduct.Milles.Value),
                                                                      Product = s.Key.Name
                                                                  }).ToList();
        }

        public List<Callage> GetCallage(DateTime? startDate, DateTime? endDate, int product, int country, int city, int area, int zone, int fsr, int pop)
        {
            IQueryable<TblPointOfPurchaseVist> query = _ctx.TblPointOfPurchaseVist.OrderBy(o => o.TblPurchase.CreatedOn);
            if (startDate != null && endDate != null)
            {
                endDate = endDate.Value.AddDays(1);
                query = query.Where(s => s.CreatedOn >= startDate.Value && s.CreatedOn <= endDate.Value);
            }
            if (product != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPurchaseItem.Where(x=>x.ProductId==product).Any());
            }
            if (country != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.TblCountry.Id == country);
            }
            if (city != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.Id == city);
            }
            if (area != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.Id == area);
            }
            if (zone != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.Id == zone);
            }
            if (pop != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.Id == pop);
            }
            if (fsr != 0)
            {
                query = query.Where(s => s.TblPurchase.TblUser.Id == fsr);
            }

            return query.GroupBy(g => new { g.TblPurchase.TblPointOfPurchase.TblZone.TblArea.Name, g.TblPurchase.TblUser.FirstName, g.TblPurchase.TblUser.LastName })
                .Select(s => new Callage
                                 {
                                     Area=s.Key.Name,
                                     SuccessfullCall = s.Where(x => x.IsSale).Count(),
                                     Call = s.Count(),
                                     EffectiveCallage =((decimal)s.Where(x => x.IsSale).Count()/ s.Count()),
                                     User=s.Key.FirstName+" "+s.Key.LastName
                                 }).ToList();
        }

        public List<NoneSaleReason> GetNoneSaleReason(DateTime? startDate, DateTime? endDate, int product, int country, int city, int area, int zone, int fsr, int pop)
        {
            IQueryable<TblPurchaseItem> query = _ctx.TblPurchaseItem.OrderBy(o => o.TblPurchase.CreatedOn);
            if (startDate != null && endDate != null)
            {
                endDate = endDate.Value.AddDays(1);
                query = query.Where(s => s.CreatedOn >= startDate.Value && s.CreatedOn <= endDate.Value);
            }
            if (product != 0)
            {
                query = query.Where(s => s.TblProduct.Id == product);
            }
            if (country != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.TblCountry.Id == country);
            }
            if (city != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.Id == city);
            }
            if (area != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.Id == area);
            }
            if (zone != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.Id == zone);
            }
            if (pop != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.Id == pop);
            }
            if (fsr != 0)
            {
                query = query.Where(s => s.TblPurchase.TblUser.Id == fsr);
            }
            var reasons = _ctx.TblReason.Where(w =>  w.StatusId!=3 && w.ReasonTypeId!=3).Select(s=>s.Id);
            return query.Where(n => reasons.Contains(n.ReasonId))
                .GroupBy(g => new { g.ReasonId, g.TblPurchase.TblPointOfPurchase.TblZone.TblArea.Name, g.TblPurchase.TblUser, })
                .Select(s => new NoneSaleReason
                                         {
                                             Area = s.Key.Name,
                                             Reason = _ctx.TblReason.FirstOrDefault(r => r.Id == s.Key.ReasonId ) != null ? _ctx.TblReason.FirstOrDefault(r => r.Id == s.Key.ReasonId).Name : "",
                                             User = s.Key.TblUser.FirstName + " " + s.Key.TblUser.LastName,
                                             Count=s.Count()
                                         }).ToList();
        }

        public List<Availability> GetAvailability(DateTime? startDate, DateTime? endDate, int product, int country, int city, int area, int zone, int fsr, int pop)
        {
            IQueryable<TblPurchaseItem> query = _ctx.TblPurchaseItem.OrderBy(o => o.TblPurchase.CreatedOn);
            if (startDate != null && endDate != null)
            {
                endDate = endDate.Value.AddDays(1);
                query = query.Where(s => s.CreatedOn >= startDate.Value && s.CreatedOn <= endDate.Value);
            }
            if (product != 0)
            {
                query = query.Where(s => s.TblProduct.Id == product);
            }
            if (country != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.TblCountry.Id == country);
            }
            if (city != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.Id == city);
            }
            if (area != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.Id == area);
            }
            if (zone != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.Id == zone);
            }
            if (pop != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.Id == pop);
            }
            if (fsr != 0)
            {
                query = query.Where(s => s.TblPurchase.TblUser.Id == fsr);
            }
            var reasons = _ctx.TblReason.Where(w => w.StatusId != 3 && w.ReasonTypeId == 2).Select(s => s.Id);
            var repo = query
                .GroupBy(
                    g =>
                    new { g.TblProduct, g.TblPurchase.TblPointOfPurchase.TblZone.TblArea, g.TblPurchase.TblUser })
                .Select(s => new Availability
                {
                    Area = s.Key.TblArea.Name,
                    Product = s.Key.TblProduct.Name,
                    Rep = s.Key.TblUser.FirstName + " " + s.Key.TblUser.LastName,
                    Available = (decimal)(s.Count(x => x.ReasonId == 0) + s.Count(x => reasons.Contains(x.ReasonId)))/s.Select(x=>x.TblPurchase.TblPointOfPurchaseVist).Count(),//_ctx.TblPointOfPurchase.Where(z=>z.TblZone.TblArea.TblFrsAssign.Any(a=>a.TblUser.Id==s.Key.TblUser.Id)).Count(),
                }).ToList();

            return repo.ToList();
        }

        public List<DropSize> GetDropSize(DateTime? startDate, DateTime? endDate, int product, int country, int city, int area, int zone, int fsr, int pop)
        {
            IQueryable<TblPurchaseItem> query = _ctx.TblPurchaseItem.OrderBy(o => o.TblPurchase.CreatedOn);
            if (startDate != null && endDate != null)
            {
                endDate = endDate.Value.AddDays(1);
                query = query.Where(s => s.CreatedOn >= startDate.Value && s.CreatedOn <= endDate.Value);
            }
            if (product != 0)
            {
                query = query.Where(s => s.TblProduct.Id == product);
            }
            if (country != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.TblCountry.Id == country);
            }
            if (city != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.Id == city);
            }
            if (area != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.Id == area);
            }
            if (zone != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.Id == zone);
            }
            if (pop != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.Id == pop);
            }
            if (fsr != 0)
            {
                query = query.Where(s => s.TblPurchase.TblUser.Id == fsr);
            }
            
            var repo = query.Where(p=>p.ReasonId==0)
                .GroupBy(
                    g =>
                    new { g.TblPurchase.TblPointOfPurchase.TblZone.TblArea, g.TblPurchase.TblUser })
                .Select(s => new DropSize
                {
                    Area = s.Key.TblArea.Name,
                    //Product = s.Key.TblProduct.Name,
                    Rep = s.Key.TblUser.FirstName + " " + s.Key.TblUser.LastName,
                    Quantity =s.Sum(p=>p.Quantity.HasValue?p.Quantity.Value:0*p.TblProduct.Milles.Value),
                }).ToList();

            return repo.ToList();
        }

        public List<SaleVolumeByPeriod> GetSaleVolumePerPeriod(DateTime? startDate, DateTime? endDate, int product, int country, int city, int area, int zone, int fsr, int pop, DateMode dateMode)
        {
            IQueryable<TblPurchaseItem> query = _ctx.TblPurchaseItem.OrderBy(o=>o.TblPurchase.CreatedOn);
            if (startDate != null && endDate != null)
            {
                endDate = endDate.Value.AddDays(1);
                query = query.Where(s => s.CreatedOn >= startDate.Value && s.CreatedOn <= endDate.Value);
            }
            if (product != 0)
            {
                query = query.Where(s => s.TblProduct.Id == product);
            }
            if (country != 0)
            {
                query =
                    query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.TblCountry.Id == country);
            }
            if (city != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.Id == city);
            }
            if (area != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.Id == area);
            }
            if (zone != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.Id == zone);
            }
            if (pop != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.Id == pop);
            }
            if (fsr != 0)
            {
                query = query.Where(s => s.TblPurchase.TblUser.Id == fsr);
            }

            List<SaleVolumeByPeriod> report = new List<SaleVolumeByPeriod>();
            switch (dateMode)
            {
                case DateMode.Weekly:
                    DateTime firstday = new DateTime(TimeUtil.GetDateTime().Year, 1, 1);
                    report = query.ToList().GroupBy(p => new {WeekNumber = (p.CreatedOn.DayOfYear - firstday.DayOfYear)/7})
                                  .Select(s => new SaleVolumeByPeriod
                                         {
                                             Quantity = s.Sum(p => p.Quantity.Value*p.TblProduct.Milles.Value),
                                             Period="Wk-"+s.Key.WeekNumber.ToString()
                                         }).ToList();
                    break;
                case DateMode.Monthly:
                    report = query.ToList().GroupBy(p => new { Month = new DateTime(p.CreatedOn.Year, p.CreatedOn.Month, 1) })
                                  .ToList()
                                  .Select(s => new SaleVolumeByPeriod
                                  {
                                      Quantity = s.Sum(p => p.Quantity.Value * p.TblProduct.Milles.Value),
                                      Period = s.Key.Month.ToString("MMM")
                                  }).ToList();
                    break;
                case DateMode.Daily:
                    report = query.ToList().GroupBy(p => new { p.CreatedOn.Date })
                                  .ToList()
                                  .Select(s => new SaleVolumeByPeriod
                                  {
                                      Quantity = s.Sum(p => p.Quantity.Value * p.TblProduct.Milles.Value),
                                      Period = s.Key.Date.ToString("dd-MMM-yy")
                                  }).ToList();
                    break;
            }


            return report;
        }

        public List<CallageByPeriod> GetCallageByPeriod(DateTime? startDate, DateTime? endDate, int product, int country, int city, int area, int zone, int fsr, int pop, DateMode dateMode)
        {
            IQueryable<TblPointOfPurchaseVist> query = _ctx.TblPointOfPurchaseVist.OrderBy(o=>o.TblPurchase.CreatedOn);
            if (startDate != null && endDate != null)
            {
                endDate = endDate.Value.AddDays(1);
                query = query.Where(s => s.CreatedOn >= startDate.Value && s.CreatedOn <= endDate.Value);
            }
            if (product != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPurchaseItem.Where(x => x.ProductId == product).Any());
            }
            if (country != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.TblCountry.Id == country);
            }
            if (city != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.Id == city);
            }
            if (area != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.Id == area);
            }
            if (zone != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.Id == zone);
            }
            if (pop != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.Id == pop);
            }
            if (fsr != 0)
            {
                query = query.Where(s => s.TblPurchase.TblUser.Id == fsr);
            }

            var report = new List<CallageByPeriod>();
            switch (dateMode)
            {
                case DateMode.Weekly:
                    DateTime firstday = new DateTime(TimeUtil.GetDateTime().Year, 1, 1);
                    report = query.ToList().GroupBy(p => new { WeekNumber = (p.CreatedOn.DayOfYear - firstday.DayOfYear) / 7 })
                                  .Select(s => new CallageByPeriod
                                  {
                                      SuccessfullCall = s.Where(x => x.IsSale).Count(),
                                      Call = s.Count(),
                                      EffectiveCallage = ((decimal)s.Where(x => x.IsSale).Count() / s.Count()),
                                      Period = "Wk-" + s.Key.WeekNumber.ToString()
                                  }).ToList();
                    break;
                case DateMode.Monthly:
                    report = query.ToList().GroupBy(p => new { Month = new DateTime(p.CreatedOn.Year, p.CreatedOn.Month, 1) })
                                  .ToList()
                                  .Select(s => new CallageByPeriod
                                  {
                                      SuccessfullCall = s.Where(x => x.IsSale).Count(),
                                      Call = s.Count(),
                                      EffectiveCallage = ((decimal)s.Where(x => x.IsSale).Count() / s.Count()),
                                      Period = s.Key.Month.ToString("MMM")
                                  }).ToList();
                    break;
                case DateMode.Daily:
                    report = query.ToList().GroupBy(p => new {p.CreatedOn.Date })
                                  .ToList()
                                  .Select(s => new CallageByPeriod
                                  {
                                      SuccessfullCall = s.Where(x => x.IsSale).Count(),
                                      Call = s.Count(),
                                      EffectiveCallage = ((decimal)s.Where(x => x.IsSale).Count() / s.Count()),
                                      Period = s.Key.Date.ToString("dd-MMM-yy")
                                  }).ToList();
                    break;
            }


            return report;
            
        }

        public List<CallageEfficiency> GetCallageEfficiency(DateTime? startDate, DateTime? endDate, int product, int country, int city, int area, int zone, int fsr, int pop)
        {
            IQueryable<TblPointOfPurchaseVist> query = _ctx.TblPointOfPurchaseVist.OrderBy(o => o.TblPurchase.CreatedOn);
            int days = 0;
            if (startDate != null && endDate != null)
            {
                days = GetNoOfTargetDays(startDate.Value, endDate.Value);
                endDate = endDate.Value.AddDays(1);
                query = query.Where(s => s.CreatedOn >= startDate.Value && s.CreatedOn <= endDate.Value);
               
            }
            if (product != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPurchaseItem.Where(x => x.ProductId == product).Any());
            }
            if (country != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.TblCountry.Id == country);
            }
            if (city != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.Id == city);
            }
            if (area != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.Id == area);
            }
            if (zone != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.Id == zone);
            }
            if (pop != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.Id == pop);
            }
            if (fsr != 0)
            {
                query = query.Where(s => s.TblPurchase.TblUser.Id == fsr);
            }

            var report= query.GroupBy(g => new { g.TblPurchase.TblPointOfPurchase.TblZone.TblArea.Name, g.TblPurchase.TblUser})
                .Select(s => new CallageEfficiency
                {
                    Area = s.Key.Name,
                    TargetCall = _ctx.TblFrsAssign.FirstOrDefault(z => z.UserId == s.Key.TblUser.Id).Target * days,//GetPOPAssigned(s.Key.TblUser.Id), //s.Where(x => x.IsSale).Count(),
                    Call = s.Count(),
                    User = s.Key.TblUser.FirstName + " " + s.Key.TblUser.LastName
                }).ToList();
            return report;
        }
        private int GetNoOfTargetDays(DateTime From, DateTime To)
        {
            int days = 0;
            for (DateTime date = From; date <= To; date = date.AddDays(1))
            {
                if (date.DayOfWeek != DayOfWeek.Sunday)
                {
                    days++;
                }
            }
            return days;
        }

        private int GetPOPAssigned(int id)
        {
            var assign = _ctx.TblFrsAssign.Where(s => s.UserId == id).FirstOrDefault();
            if (assign == null)
                return 0;
            var zones = new List<int>();
            if (assign.MondayZone.HasValue)
                zones.Add(assign.MondayZone.Value);
            if (assign.TuesdayZone.HasValue)
                zones.Add(assign.TuesdayZone.Value);
            if (assign.WednesdayZone.HasValue)
                zones.Add(assign.WednesdayZone.Value);
            if (assign.ThursdayZone.HasValue)
                zones.Add(assign.ThursdayZone.Value);
            if (assign.FridayZone.HasValue)
                zones.Add(assign.FridayZone.Value);
            if (assign.SaturdayZone.HasValue)
                zones.Add(assign.SaturdayZone.Value);
            if (assign.SundayZone.HasValue)
                zones.Add(assign.SundayZone.Value);
            zones = zones.Distinct().ToList();
            int count = _ctx.TblPointOfPurchase.Where(s => zones.Contains(s.ZoneId)).Count();

            return count;
        }

        public List<CompareProduct> GetCompareProduct(DateTime? startDate, DateTime? endDate, int productA, int productB, int country, int city, int area, int zone, int fsr, int pop)
        {
            IQueryable<TblPurchaseItem> query = _ctx.TblPurchaseItem.OrderBy(o => o.TblPurchase.CreatedOn);
            if (startDate != null && endDate != null)
            {
                endDate = endDate.Value.AddDays(1);
                query = query.Where(s => s.CreatedOn >= startDate.Value && s.CreatedOn <= endDate.Value);
            }
            if (productA != 0 && productB!=0)
            {
                query = query.Where(s =>s.TblProduct.Id == productA || s.TblProduct.Id==productB);
            }
            if (country != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.TblCountry.Id == country);
            }
            if (city != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.Id == city);
            }
            if (area != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.Id == area);
            }
            if (zone != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.Id == zone);
            }
            if (pop != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.Id == pop);
            }
            if (fsr != 0)
            {
                query = query.Where(s => s.TblPurchase.TblUser.Id == fsr);
            }
            return query.GroupBy(g => new { g.ProductId, g.TblProduct.Name }).Select(s => new CompareProduct
            {
                Quantity = s.Sum(m => m.Quantity.Value * m.TblProduct.Milles.Value),
                Product = s.Key.Name
            }).ToList();
        }

        public List<OutletVisit> GetOutletVisit(DateTime? startDate, DateTime? endDate, int product, int country, int city, int area, int zone, int fsr, int pop)
        {
            IQueryable<TblPointOfPurchaseVist> query = _ctx.TblPointOfPurchaseVist.OrderBy(o => o.TblPurchase.CreatedOn);
            if (startDate != null && endDate != null)
            {
                endDate = endDate.Value.AddDays(1);
                query = query.Where(s => s.CreatedOn >= startDate.Value && s.CreatedOn <= endDate.Value);
            }
            if (product != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPurchaseItem.Where(x => x.ProductId == product).Any());
            }
            if (country != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.TblCountry.Id == country);
            }
            if (city != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.Id == city);
            }
            if (area != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.Id == area);
            }
            if (zone != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.Id == zone);
            }
            if (pop != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.Id == pop);
            }
            if (fsr != 0)
            {
                query = query.Where(s => s.TblPurchase.TblUser.Id == fsr);
            }

            return query.GroupBy(g => new { g.TblPurchase.TblPointOfPurchase.Name, g.VistDate, g.TblPurchase.TblUser})
                .Select(s => new OutletVisit
                {
                    Name = s.Key.Name,
                    DateVisited = s.Key.VistDate,
                    NoOfVist = s.Count(),
                    Fsr=s.Key.TblUser.FirstName+" "+s.Key.TblUser.LastName,
                }).OrderBy(s=>s.Name).ToList();
        }

        public List<OutletVisitSaleAndHasStock> GetOutletVisitSaleAndHasStock(DateTime? startDate, DateTime? endDate, int product, int country, int city, int area, int zone, int fsr, int pop)
        {
            IQueryable<TblPointOfPurchaseVist> query = _ctx.TblPointOfPurchaseVist.OrderBy(o => o.TblPurchase.CreatedOn);
            if (startDate != null && endDate != null)
            {
                endDate = endDate.Value.AddDays(1);
                query = query.Where(s => s.CreatedOn >= startDate.Value && s.CreatedOn <= endDate.Value);
            }
            if (product != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPurchaseItem.Where(x => x.ProductId == product).Any());
            }
            if (country != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.TblCountry.Id == country);
            }
            if (city != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.TblCity.Id == city);
            }
            if (area != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.TblArea.Id == area);
            }
            if (zone != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.TblZone.Id == zone);
            }
            if (pop != 0)
            {
                query = query.Where(s => s.TblPurchase.TblPointOfPurchase.Id == pop);
            }
            if (fsr != 0)
            {
                query = query.Where(s => s.TblPurchase.TblUser.Id == fsr);
            }
              var reasons = _ctx.TblReason.Where(w =>  w.StatusId!=3 && w.ReasonTypeId==(int)ReasonType.HasStock).Select(s=>s.Id);
            List<OutletVisitSaleAndHasStock> report = new List<OutletVisitSaleAndHasStock>();
            foreach (var r in query.OrderBy(s=>s.TblPurchase.TblPointOfPurchase.Name))
            {
                OutletVisitSaleAndHasStock item = new OutletVisitSaleAndHasStock();
                if (r.IsSale || r.TblPurchase.TblPurchaseItem.Any(s => reasons.Contains(s.ReasonId)))
                {
                    item.Name = r.TblPurchase.TblPointOfPurchase.Name;
                    if (r.IsSale)
                        item.Type = "SoldTo";
                    else
                        item.Type = "Has a Stock";
                    report.Add(item);
                }

            }
            return report;
        }
    }
}
